import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import dao.CartDao;
import dao.UserDao;
import entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import utils.JdbcUtilsV1;

import javax.management.Query;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

public class JunitTest {

    // 单元测试
    @Test
    public void test() throws SQLException {
        // 创建连接池对象
        DruidDataSource druidDataSource = new DruidDataSource();
        // 必须配置
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/java");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("rjxy");
        // 选配
        druidDataSource.setInitialSize(5);
        druidDataSource.setMinIdle(5);
        druidDataSource.setMaxActive(10);
        druidDataSource.setMaxWait(60000);
        DruidPooledConnection connection = druidDataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

    @Test
    public void test1() throws Exception {
        // 类加载读取配置文件
        InputStream resourceAsStream = JunitTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        // 创建连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

    @Test
    public void test2() throws SQLException {
        Connection connection = JdbcUtilsV1.getConnection();
        System.out.println(connection);
        connection.close();
    }

    @Test
    public void test3() throws SQLException {
        UserDao userDao = new UserDao();
        CartDao cartDao = new CartDao();
        Connection connection = JdbcUtilsV1.getConnection();
        // 添加、修改、删除   事务
        connection.setAutoCommit(false);
        try {
            userDao.insert(connection);
            cartDao.insert(connection);
        } catch (RuntimeException e) {
            // 事务回滚
            connection.rollback();
            throw new RuntimeException(e);
        } finally {
            connection.commit();
            connection.setAutoCommit(true);
            connection.close();
        }
    }

    @Test
    public void test4() throws SQLException {
        // 添加
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "insert into user(username,password) values(?,?)";
        int update = queryRunner.update(connection, sql, "rjxy", "rjxy");
        System.out.println(update);
        JdbcUtilsV1.close();
    }
    @Test
    public void test5() throws SQLException {
        // 删除
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from user where username = ?";
        int update = queryRunner.update(connection, sql, "rjxy");
        System.out.println(update);
        JdbcUtilsV1.close();
    }

    @Test
    public void test6() throws SQLException {
        // 更新
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update user set password = ? where username = ?";
        int update = queryRunner.update(connection, sql, "123", "lisi");
        System.out.println(update);
        JdbcUtilsV1.close();
    }

    @Test
    public void test7() throws SQLException {
        Connection connection = JdbcUtilsV1.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // 查询 单行数据
        String sql = "select * from user where username = ?";
//        BeanHandler<User> userBeanHandler = new BeanHandler<>(User.class);
        User user = queryRunner.query(connection, sql, new BeanHandler<>(User.class), "lisi");
        System.out.println(user);

//        ResultSetHandler<User> userResultSetHandler = new ResultSetHandler<>() {
//            @Override
//            public User handle(ResultSet resultSet) throws SQLException {
//                while (resultSet.next()) {
//                    User user = new User();
//                    user.setId(resultSet.getLong("id"));
//                    user.setUsername(resultSet.getString("username"));
//                    user.setPassword(resultSet.getString("password"));
//                    return user;
//                }
//                return null;
//            }
//        };
//        User user = queryRunner.query(connection, sql, userResultSetHandler, "admin");
//        System.out.println(user);

        // 查询多行数据 集合
//        String sql = "select * from user";
//        BeanListHandler<User> userBeanListHandler = new BeanListHandler<>(User.class);
//        List<User> users = queryRunner.query(connection, sql, userBeanListHandler);
//        System.out.println(users);
        // 查询单个数据
//        String sql = "select count(*) from user";
//        ScalarHandler<Long> longScalarHandler = new ScalarHandler<>();
//        Long count = queryRunner.query(connection, sql, longScalarHandler);
//        System.out.println(count);
        JdbcUtilsV1.close();
    }
}
